package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@SuppressWarnings("rawtypes")
public class ExcelTool {
	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";

	public static void main(String[] args) {

		Map<String, String> dataMap = new HashMap<String, String>();
		dataMap.put("Addr", "Addr123");
		dataMap.put("BankName", "BankName123");
		dataMap.put("Phone", "Phone123");
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		list.add(dataMap);
		list.add(dataMap);
		list.add(dataMap);
		writeExcel(list, "D:/writeExcel.xlsx");
	}

	/**
	 * 写出excel
	 * @param pageinfoList
	 * @param finalXlsxPath
	 */
	public static void writeExcel(List<Map<String, String>> pageinfoList, String finalXlsxPath) {
		OutputStream out = null;
		try {
			// 读取Excel文档
			File finalXlsxFile = new File(finalXlsxPath);
			Workbook workBook = getWorkbook(finalXlsxFile);
			// sheet 对应一个工作页
			Sheet sheet;
			if (workBook.getNumberOfSheets() != 0)
				sheet = workBook.getSheetAt(0);
			else
				sheet = workBook.createSheet();
			/**
			 * 删除原有数据，除了属性列
			 */
			int rowNumber = sheet.getLastRowNum(); // 第一行从0开始算
			for (int i = 0; i <= rowNumber && rowNumber != 0; i++) {
				Row row = sheet.getRow(i);
				sheet.removeRow(row);
			}
			// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
			out = new FileOutputStream(finalXlsxPath);
			workBook.write(out);
			/**
			 * 往Excel中写新数据
			 */
			// 插入属性行
			Row row = sheet.createRow(0);
			int i = 0;
			for (Object columns : pageinfoList.get(0).keySet()) {
				row.createCell(i).setCellValue((String) columns);
				i++;
			}
			// 插入数据
			for (int j = 0; j < pageinfoList.size(); j++) {
				// 创建一行：从第二行开始，跳过属性列
				row = sheet.createRow(j + 1);
				// 得到要插入的每一条记录
				Map dataMap = pageinfoList.get(j);
				int k = 0;
				for (Object columns : dataMap.values()) {
					Cell cell = row.createCell(k);
					cell.setCellValue((String) columns);
					k++;
				}
			}
			// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
			out = new FileOutputStream(finalXlsxPath);
			workBook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (out != null) {
					out.flush();
					out.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param in
	 * @param filename
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbook(File file) throws IOException {
		Workbook wb = null;
		if (!file.exists()) {
			file.createNewFile();
			return new XSSFWorkbook();
		}
		FileInputStream in = new FileInputStream(file);
		if (file.getName().endsWith(EXCEL_XLS)) { // Excel&nbsp;2003
			wb = new HSSFWorkbook(in);
		} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}
}
